﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.Text.RegularExpressions;

namespace GeneratorLibrary.DatabaseCompare {
    public partial class DiffForm : Form {
        private ListViewColumnSorter lvwColumnSorter;
        public DiffForm() {
            InitializeComponent();
            lvwColumnSorter = new ListViewColumnSorter();
            this.listView1.ListViewItemSorter = lvwColumnSorter;

        }
        private void DiffForm_Load(object sender, EventArgs e) {
            bindDatabases(1, false);
            bindDatabases(2, false);
        }

        DataRefKeyedColl objects;

        private void btnCompare_Click(object sender, EventArgs e) {
            listView1.Items.Clear();
            txtSource.Clear();
            txtDestination.Clear();
            btnClearTransaction.Enabled = true;
            grpSource.Enabled = false;
            grpDestination.Enabled = false;
            btnCompare.Enabled = false;

            this.Cursor = Cursors.WaitCursor;

            this.Refresh();

            Database db1 = new Server(txtServer.Text).Databases[txtDatabase.Text];
            Database db2 = new Server(txtServer2.Text).Databases[txtDatabase2.Text];

            objects = new DataRefKeyedColl();

            ScriptingOptions o = new ScriptingOptions();
            o.IncludeIfNotExists = false;
            o.Indexes = true;
            o.ClusteredIndexes = true;
            o.Default = true;
            o.DriAll = true;
            
            if (chkTables.Checked) {

                foreach (Table item in db1.Tables) {
                    
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "table");
                    dr.Script1 = sb.ToString();
                    objects.Add(dr);
                }
                foreach (Table item in db2.Tables) {
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "table");
                    if (objects.Contains(dr.Key)) {
                        dr = objects[dr.Key];
                        dr.Script2 = sb.ToString();
                    } else {
                        dr.Script2 = sb.ToString();
                        objects.Add(dr);
                    }
                }
            }
            if (chkViews.Checked) {

                foreach (Microsoft.SqlServer.Management.Smo.View item in db1.Views) {
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "view");
                    dr.Script1 = sb.ToString();
                    objects.Add(dr);
                }
                foreach (Microsoft.SqlServer.Management.Smo.View item in db2.Views) {
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "view");
                    if (objects.Contains(dr.Key)) {
                        dr = objects[dr.Key];
                        dr.Script2 = sb.ToString();
                    } else {
                        dr.Script2 = sb.ToString();
                        objects.Add(dr);
                    }
                }
            }
            if (chkProcedures.Checked) {
                foreach (StoredProcedure item in db1.StoredProcedures) {
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "proc");
                    dr.Script1 = sb.ToString();
                    objects.Add(dr);
                }
                foreach (StoredProcedure item in db2.StoredProcedures) {
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "proc");
                    if (objects.Contains(dr.Key)) {
                        dr = objects[dr.Key];
                        dr.Script2 = sb.ToString();
                    } else {
                        dr.Script2 = sb.ToString();
                        objects.Add(dr);
                    }
                }
            }
            if (chkFunctions.Checked) {
                foreach (UserDefinedFunction item in db1.UserDefinedFunctions) {
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "func");
                    dr.Script1 = sb.ToString();
                    objects.Add(dr);
                }
                foreach (UserDefinedFunction item in db2.UserDefinedFunctions) {
                    if (item.IsSystemObject)
                        continue;
                    StringBuilder sb = new StringBuilder();
                    foreach (string str in item.Script(o)) {
                        sb.AppendLine(str);
                    }
                    DataRef dr = new DataRef(item, "func");
                    if (objects.Contains(dr.Key)) {
                        dr = objects[dr.Key];
                        dr.Script2 = sb.ToString();
                    } else {
                        dr.Script2 = sb.ToString();
                        objects.Add(dr);
                    }
                }
            }
            bindObjects();
            this.Cursor = Cursors.Default;
        }

        private void bindObjects() {
            listView1.Items.Clear();
            txtSource.Clear();
            txtDestination.Clear();
            int i = 0;
            foreach (DataRef item in objects) {
                string[] ary = item.DoCompare();
                ary[4] = i.ToString();
                i++;
                if (item.ComparisonValue != ComparisonValue.Equal || !chkDifferencesOnly.Checked) {
                    listView1.Items.Add(new ListViewItem(ary));
                }
            }
        }

        private void btnLogin_Click(object sender, EventArgs e) {
            bindDatabases(1);
        }

        private void btnLogin2_Click(object sender, EventArgs e) {
            bindDatabases(2);
        }

        private void bindDatabases(int db) {
            bindDatabases(db, true);
        }
        private void bindDatabases(int db, bool showError) {
            try {
                if (db == 1){
                    txtDatabase.DataSource = getNames(new Server(txtServer.Text).Databases);
                } else {
                    txtDatabase2.DataSource = getNames(new Server(txtServer2.Text).Databases);
                }
            } catch (Exception ex) {
                if (showError)
                    MessageBox.Show("Login Failed: " + ex.Message, "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private List<string> getNames(DatabaseCollection databases) {
            List<string> names = new List<string>();
            foreach (Database d in databases) {
                names.Add(d.Name);
            }
            return names;
        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e) {
            if (listView1.SelectedItems.Count > 0) {
                DataRef item = objects[int.Parse(listView1.SelectedItems[0].SubItems[4].Text)];

                if (item.ComparisonValue != ComparisonValue.Different) {
                    txtSource.Text = item.Script1;
                    txtDestination.Text = item.Script2;
                    btnAlterSource.Enabled = false;

                } else {
                    btnAlterSource.Enabled = true;

                    string[] lines1 = item.Script1.Split(new char[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
                    string[] lines2 = item.Script2.Split(new char[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
                    int diff1, diff2;
                    AddLines(lines1, lines2, txtSource, out diff1);
                    AddLines(lines2, lines1, txtDestination, out diff2);
                    if (diff1 > diff2) {
                        diff1 = diff2;
                    }
                    txtSource.SelectionStart = diff1;
                    txtDestination.SelectionStart = diff1;
                    txtSource.ScrollToCaret();
                    txtDestination.ScrollToCaret();
                }
            } else {
                txtSource.Text = "";
                txtDestination.Text = "";
            }
        }
        Regex r = new Regex("\\s\\s+");
        private void AddLines(string[] lines, string[] comparison, RichTextBox txt, out int firstDiff) {
            firstDiff = int.MaxValue;
            foreach (string s in lines) {
                string s2 = s.Trim(new char[] { ',', ' ', '\t', '\r', '\n' }).Replace("[", "").Replace("]", "").Replace("dbo.", "");
                bool found = false;
                if (s2.Length > 0) {
                    foreach (string s3 in comparison) {
                        if (s3.Replace("[", "").Replace("]", "").Replace("dbo.", "").Contains(s2)) {
                            found = true;
                            break;
                        }
                    }
                } else {
                    found = true;
                }
                if (found) {
                    txt.AppendText(s);
                } else {
                    if (txt.TextLength < firstDiff)
                        firstDiff = txt.TextLength;
                    txt.SelectionStart = txt.TextLength;
                    Color original = txt.SelectionBackColor;
                    txt.SelectionBackColor = Color.Yellow;
                    txt.AppendText(s);
                    txt.SelectionStart = txt.TextLength;
                    txt.SelectionBackColor = RichTextBox.DefaultBackColor;
                }
                txt.AppendText(Environment.NewLine);
            }
            if (firstDiff == int.MaxValue)
                firstDiff = 0;
        }

        private void btnClearTransaction_Click(object sender, EventArgs e) {
            if (MessageBox.Show("Are you sure?", "Are you sure?", MessageBoxButtons.OKCancel) == DialogResult.OK) {
                grpSource.Enabled = true;
                grpDestination.Enabled = true;
                listView1.Items.Clear();
                txtSource.Clear();
                txtDestination.Clear();
                btnCompare.Enabled = true;
                btnClearTransaction.Enabled = false;
            }
        }

        


        private void btnAlterSource_Click(object sender, EventArgs e) {
            DataRef item = objects[int.Parse(listView1.SelectedItems[0].SubItems[4].Text)];
            switch (item.ObjectType) {
                case "table":
                    Server srv2 = new Server(txtServer2.Text);
                    Database db1 = new Server(txtServer.Text).Databases[txtDatabase.Text];
                    Database db2 = srv2.Databases[txtDatabase2.Text];
                    Table t = db1.Tables[item.Name];
                    Table t2 = db2.Tables[item.Name];

                    AlterTable tbl = new AlterTable();
                    txtSource.Text = 
                        String.Join("\n--colinrocks\n", tbl.doAlterTable(t, t2, srv2).ToArray());
                    break;
            }

            //Database db1 = new Server(txtServer.Text).Databases[txtDatabase.Text];
            //Database db2 = new Server(txtServer2.Text).Databases[txtDatabase2.Text];

            //DataRef item = objects[int.Parse(listView1.SelectedItems[0].SubItems[4].Text)];
            //switch (item.ObjectType) {
            //    case "table":
            //        Table t = db1.Tables[item.Name];
            //        Table t2 = db1.Tables[item.Name];
            //        foreach (Column c in t.Columns) {
            //            foreach (Column c2 in t2.Columns) {
            //                if (c.Name == c2.Name) {
            //                    break;
            //                }
            //            }
            //        }

            //        break;
            //}
        }

        private void chkDifferencesOnly_CheckedChanged(object sender, EventArgs e) {
            if (!btnCompare.Enabled) {
                bindObjects();
            }
        }
        private void listView1_ColumnClick(object sender, ColumnClickEventArgs e) {
            if (e.Column == lvwColumnSorter.SortColumn) {
                // Reverse the current sort direction for this column.
                if (lvwColumnSorter.Order == System.Windows.Forms.SortOrder.Ascending) {
                    lvwColumnSorter.Order = System.Windows.Forms.SortOrder.Descending;
                } else {
                    lvwColumnSorter.Order = System.Windows.Forms.SortOrder.Ascending;
                }
            } else {
                // Set the column number that is to be sorted; default to ascending.
                lvwColumnSorter.SortColumn = e.Column;
                lvwColumnSorter.Order = System.Windows.Forms.SortOrder.Ascending;
            }

            // Perform the sort with these new sort options.
            this.listView1.Sort();

        }
    }
}
